﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Document_Management
{
     public class PublicClass
     {
         static string connectionstring = ConfigurationManager.ConnectionStrings["庫存管理"].ConnectionString;
         public static byte[] encryptData(String data)
         {
             System.Security.Cryptography.MD5CryptoServiceProvider md5shader = new System.Security.Cryptography.MD5CryptoServiceProvider();
             byte[] hashedbyte;
             System.Text.UTF8Encoding encoder = new UTF8Encoding();
             hashedbyte = md5shader.ComputeHash(encoder.GetBytes(data));
             return hashedbyte;
         }
         public static string md5(string data)
         {
             return BitConverter.ToString(encryptData(data)).Replace("-", "").ToLower();
         }
       
        static public void setRowNumber(DataGridView dgv)
        {
           
            foreach (DataGridViewRow row in dgv.Rows)
            {
                
                row.HeaderCell.Value = (row.Index + 1).ToString();
                row.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                if (row.Index % 2 == 0) row.DefaultCellStyle.BackColor = Color.Azure;
                else row.DefaultCellStyle.BackColor = Color.Beige;
                
            }
            dgv.EnableHeadersVisualStyles = false;
            dgv.ColumnHeadersDefaultCellStyle.BackColor = Color.AntiqueWhite;
           // dgv.ColumnHeadersDefaultCellStyle.ForeColor = Color.Red;
            dgv.AllowUserToAddRows = false;
            dgv.AllowUserToDeleteRows = false;
            dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dgv.BackgroundColor = Color.White;
            
        }
        
         //tianche
        static public void Insert(string strcmd)
        {

            SqlConnection con = new SqlConnection(connectionstring);
            try
            {
                con.Open();
                
                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.ExecuteNonQuery();
                MessageBox.Show("新增成功", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }
         //all in one
        static public void InsertEditDeleteCommand(string ActionName,string strcmd,string TablenameorView,DataGridView dtgv)
        {

            SqlConnection con = new SqlConnection(connectionstring);

            if (ActionName == "刪除")
            {
                DialogResult result = MessageBox.Show("您確定要刪除這個項目碼？", "警報", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                if (result == DialogResult.OK)
                    try
                    {
                        con.Open();

                        SqlCommand cmd = new SqlCommand(strcmd, con);
                        cmd.ExecuteNonQuery();
                        MessageBox.Show(ActionName + "成功", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        string sqlall = String.Format("select * from {0}", TablenameorView);
                        //LoadAllNoneProcedure(sqlall, dtgv);
                        SqlDataAdapter da = new SqlDataAdapter(sqlall, con);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        dtgv.DataSource = dt;

                        setRowNumber(dtgv);
                        con.Close();

                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                        con.Close();
                    }
                else return;
            }
            else
            {
                try
                {
                    con.Open();

                    SqlCommand cmd = new SqlCommand(strcmd, con);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show(ActionName + "成功", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    string sqlall = String.Format("select * from {0}", TablenameorView);
                    //LoadAllNoneProcedure(sqlall, dtgv);
                    SqlDataAdapter da = new SqlDataAdapter(sqlall, con);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    dtgv.DataSource = dt;

                    setRowNumber(dtgv);
                    con.Close();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    con.Close();
                }
            }
        }

        static public void Edit(string strcmd)
        {

            SqlConnection con = new SqlConnection(connectionstring);
            try
            {
                con.Open();

                SqlCommand cmd = new SqlCommand(strcmd, con);
                cmd.ExecuteNonQuery();
                MessageBox.Show("更新成功", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        static public void Delete(string strcmd)
        {
            DialogResult result = MessageBox.Show("您確定要刪除這個項目碼？","警報",MessageBoxButtons.OKCancel,MessageBoxIcon.Warning);
            if (result == DialogResult.OK)
            {
                SqlConnection con = new SqlConnection(connectionstring);
                try
                {
                    con.Open();

                    SqlCommand cmd = new SqlCommand(strcmd, con);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("刪除成功", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    con.Close();
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    con.Close();
                }
            }
            else return;
        }

        static public void LoadAllNoneProcedure(string tablename,DataGridView dtgv)
        {

            SqlConnection con = new SqlConnection(connectionstring);
            try
            {
                con.Open();
                string strcmd = String.Format("select * from {0}",tablename);
                SqlDataAdapter cmd = new SqlDataAdapter(strcmd, con);
                DataTable dt = new DataTable();
                cmd.Fill(dt);
                dtgv.DataSource = dt;

                setRowNumber(dtgv);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }

        static public void LoadAllNoneProcedure_strcmd(string strcmd, DataGridView dtgv)
        {

            SqlConnection con = new SqlConnection(connectionstring);
            try
            {
                con.Open();

                SqlDataAdapter cmd = new SqlDataAdapter(strcmd, con);
                //cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                cmd.Fill(dt);
                dtgv.DataSource = dt;

                setRowNumber(dtgv);
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                con.Close();
            }
        }
        static public void SearchByTwo(string tablename,string field1,string field2,DataGridView dtgv,  string tiancheid,DateTime from1,DateTime to1,DateTime from2,DateTime to2)
        {
            try
            {
                string strcmd = String.Format("select * from {0} where 天車編號 like '%'+N'{1}'+'%' and {2}>='{3}' and {2}<='{4}'and {5}>='{6}' and {5}<='{7}'",tablename,tiancheid,field1,from1.ToShortDateString(),to1.ToShortDateString(),field2,from2.ToShortDateString(),to2.ToShortDateString());
                LoadAllNoneProcedure_strcmd(strcmd, dtgv);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        static public void SearchByOne(string tablename, string field1, DataGridView dtgv, string tiancheid, DateTime from, DateTime to)
        {
            try
            {
                string strcmd = String.Format("select * from {0} where 天車編號 like '%'+N'{1}'+'%' and {2}>='{3}' and {2}<='{4}'", tablename, tiancheid, field1, from.ToShortDateString(), to.ToShortDateString());
                LoadAllNoneProcedure_strcmd(strcmd, dtgv);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        static public void SearchbyTianchebianhao(DataGridView dtgv, string tianchebianhao,string edinghezhongmicheng,string gongnengmingcheng,string changpaimingcheng,string shengchanxuhao,DateTime shengchangriqiFrom,DateTime shengchanriqiTo)
        {
            try
            {
                string strcmd = String.Format("select 天車.天車編號,天車.生產序號,天車.生產日期,天車廠牌.廠牌名稱,天車功能.功能名稱,天車額定荷重.額定荷重名稱 from 天車,天車廠牌,天車功能,天車額定荷重 where 天車.廠牌代號=天車廠牌.廠牌代號 and 天車.功能代號=天車功能.功能代號 and 天車.額定荷重代號=天車額定荷重.額定荷重代號 and 天車.天車編號 like '%'+N'{0}'+'%' and 天車.生產序號 like '%'+N'{1}'+'%' and 天車.生產日期>=N'{2}' and 天車.生產日期<=N'{3}' and 天車廠牌.廠牌名稱 like '%'+N'{4}'+'%' and 天車功能.功能名稱 like '%'+N'{5}'+'%' and 天車額定荷重.額定荷重名稱 like '%'+N'{6}'+'%'", tianchebianhao, shengchanxuhao, shengchangriqiFrom.ToShortDateString(), shengchanriqiTo.ToShortDateString(), changpaimingcheng, gongnengmingcheng, edinghezhongmicheng);
                LoadAllNoneProcedure_strcmd(strcmd, dtgv);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        static public void SearchTiancherenyuan(DataGridView dtgv,string renyuandaihao,string renyuanxingming,string bumenmingcheng,string chengbenzhongxinmingcheng,string dianhuahaoma)
        {
            try
            {
                string strcmd = String.Format("select 天車人員.天車人員代號,天車人員.人員姓名,天車部門.部門名稱,天車成本中心.天車成本中心名稱,天車人員.電話號碼 from 天車人員,天車部門,天車成本中心 where 天車人員.部門代號=天車部門.部門代號 and 天車人員.成本中心代號=天車成本中心.成本中心代號 and  天車人員.天車人員代號 like '%'+N'{0}'+'%' and 天車人員.人員姓名 like '%'+N'{1}'+'%' and (天車部門.部門名稱 like '%'+N'{2}'+'%' or 天車部門.部門代號 like '%'+N'{2}'+'%') and (天車成本中心.天車成本中心名稱 like '%'+N'{3}'+'%' or 天車成本中心.成本中心代號 like '%'+N'{3}'+'%') and 天車人員.電話號碼 like '%'+N'{4}'+'%'", renyuandaihao, renyuanxingming, bumenmingcheng, chengbenzhongxinmingcheng, dianhuahaoma);
                LoadAllNoneProcedure_strcmd(strcmd, dtgv);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        static public void SearchTiancheInfo(DataGridView dtgv,string tianchebianhao,string edinghezhong,string gongneng,string changpai,string shengchanxuhao,DateTime shengchanriqifrom,DateTime shengchanriqito,DateTime anzhuangriqifrom,DateTime anzhuangriqito,DateTime chuciquzhengfrom,DateTime chuciquzhengto,string bumen,string chengbenzhongxin,string tiancherenyuan,string dianhua)
        {
            try
            {
                string strcmd = String.Format("select 天車編號,額定荷重名稱,功能名稱,廠牌名稱,生產序號,生產日期,安裝日期,初次取證日,天車人員代號,人員姓名,部門名稱,天車成本中心名稱,電話號碼 from ViewtiancheinfoSearch where 天車編號 like '%'+N'{0}'+'%' and 額定荷重名稱 like '%'+N'{1}'+'%' and 功能名稱 like '%'+N'{2}'+'%' and 廠牌名稱 like '%'+N'{3}'+'%' and 生產序號 like '%'+N'{4}'+'%' and (天車人員代號 like '%'+N'{5}'+'%' or 人員姓名 like '%'+N'{5}'+'%') and 電話號碼 like '%'+N'{6}'+'%' and (部門代號 like '%'+N'{7}'+'%' or 部門名稱 like '%'+N'{7}'+'%') and (天車成本中心名稱 like '%'+N'{8}'+'%' or 成本中心代號 like '%'+N'{8}'+'%') and (生產日期>=N'{9}' and 生產日期<=N'{10}') and (安裝日期>=N'{11}' and 安裝日期<=N'{12}') and (初次取證日>=N'{13}' and 初次取證日<=N'{14}')", tianchebianhao, edinghezhong, gongneng, changpai, shengchanxuhao, tiancherenyuan, dianhua, bumen, chengbenzhongxin, shengchanriqifrom.ToShortDateString(), shengchanriqito.ToShortDateString(), anzhuangriqifrom.ToShortDateString(), anzhuangriqito.ToShortDateString(), chuciquzhengfrom.ToShortDateString(), chuciquzhengto.ToShortDateString());
                LoadAllNoneProcedure_strcmd(strcmd, dtgv);
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
           
            // string strcmd = String.Format("select 天車編號,額定荷重名稱,功能名稱,廠牌名稱,生產序號,生產日期,安裝日期,初次取證日,預保日,巡檢日期,點檢日期,維修日期 from ViewtiancheinfoSearch where 天車編號 like '%'+N'{0}'+'%' and 額定荷重名稱 like '%'+N'{1}'+'%' and 功能名稱 like '%'+N'{2}'+'%' and 廠牌名稱 like '%'+N'{3}'+'%' and 生產序號 like '%'+N'{4}'+'%' and (天車人員代號 like '%'+N'{5}'+'%' or 人員姓名 like '%'+N'{5}'+'%') and 電話號碼 like '%'+N'{6}'+'%' and (部門代號 like '%'+N'{7}'+'%' or 部門名稱 like '%'+N'{7}'+'%') and (天車成本中心名稱 like '%'+N'{8}'+'%' or 成本中心代號 like '%'+N'{8}'+'%') and (預保日>=N'{9}' and 預保日<=N'{10}' or 預保日 is NULL ) and (巡檢日期>=N'{11}' and 巡檢日期<=N'{12}' or 巡檢日期 is NULL ) and (點檢日期>=N'{13}' and 點檢日期<=N'{14}' or 點檢日期 is NULL) and (維修日期>=N'{15}' and 維修日期<=N'{16}' or 維修日期 is NULL) and (生產日期>=N'{17}' and 生產日期<=N'{18}') and (安裝日期>=N'{19}' and 安裝日期<=N'{20}') and (初次取證日>=N'{21}' and 初次取證日<=N'{22}')", tianchebianhao, edinghezhong, gongneng, changpai, shengchanxuhao, tiancherenyuan, dianhua, bumen, chengbenzhongxin, yubaorifrom, yubaorito, xunjianrifrom, xunjianrito, dianjianrifrom, dianjianrito, weixiurifrom, weixiurito, shengchanriqifrom, shengchanriqito, anzhuangriqifrom, anzhuangriqito, chuciquzhengfrom, chuciquzhengto);
            // static public void SearchTiancheInfo(DataGridView dtgv,string tianchebianhao,string edinghezhong,string gongneng,string changpai,string shengchanxuhao,DateTime shengchanriqifrom,DateTime shengchanriqito,DateTime anzhuangriqifrom,DateTime anzhuangriqito,DateTime chuciquzhengfrom,DateTime chuciquzhengto,DateTime yubaorifrom,DateTime yubaorito,DateTime xunjianrifrom, DateTime xunjianrito,DateTime dianjianrifrom,DateTime dianjianrito,DateTime weixiurifrom,DateTime weixiurito,string bumen,string chengbenzhongxin,string tiancherenyuan,string dianhua)
        }
    }
}
